package com.atzon.DAO;

import com.atzon.constant.Const;
import com.atzon.model.Article;
import com.atzon.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ArtDAO extends BaseDAO {
    public void delFavById(Integer uId, Integer aId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            qr.update("delete from favorite where u_id = ? and a_id = ? ", uId, aId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updateArt(Integer aId, Integer count) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            qr.update("update article set browse_count = ? where id = ? ", count, aId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void addArt(String title, String content, String username, Integer userId) {
        try {
            QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
            Date now = new Date();
            qr.update("insert into article value(?,?,?,?,?,?,?)",
                    null, title, content, 0, now, username, userId);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public long getTotalArt(Integer userId) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            String sql = "select count(*) as total from article where user_id = " + userId;
            Map<String, Object> map = qr.query(sql, new MapHandler());
            total = Long.parseLong(map.get("total").toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    public long getTotalFav(Integer userId) {
        long total = 0L;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            String sql = "select count(*) as total from favorite where u_id = " + userId;
            Map<String, Object> map = qr.query(sql, new MapHandler());
            total = Long.parseLong(map.get("total").toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    public Article getArtById(Integer aId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select id, title, content, browse_count as browseCount, ");
            sql.append("publish_date as publishDate, publish_username as publishUsername, user_id as userId ");
            sql.append("from article where id = ? ");
            Article article = qr.query(sql.toString(), new BeanHandler<Article>(Article.class), aId);
            System.out.println(article);
            if (article != null) {
                return article;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Map<String, Object> findArtById(Integer artId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        Map<String, Object> map = null;
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select art.id as artId, art.title as artTitle,art.content as artCont,art.browse_count as artCount,");
            sql.append("art.publish_date as artDate,art.publish_username as artName,art.user_id as userId,");
            sql.append("(select count(*) as total from favorite fav where art.id = fav.a_id) as follows, ");
            sql.append("ui.real_name as userName ");
            sql.append("from article art ");
            sql.append("left join user_info ui on art.user_id = ui.user_id ");
            sql.append("where art.id = ? ");
            map = qr.query(sql.toString(), new MapHandler(), artId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public List<Map<String, Object>> findFavUser(Integer artId) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select fav.u_id as uId, ui.real_name as realName ");
            sql.append("from favorite fav ");
            sql.append("left join user_info ui on fav.u_id = ui.user_id ");
            sql.append("where fav.a_id = ? ");
            maps = qr.query(sql.toString(), new MapListHandler(), artId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }

    public List<Map<String, Object>> findArt(Integer userId, int page, String search) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select art.id as artId, art.title as artTitle,art.content as artCont,art.browse_count as artCount,");
            sql.append("art.publish_date as artDate,art.publish_username as artName,art.user_id as userId,");
            sql.append("(select count(*) as total from favorite fav where art.id = fav.a_id) as follows, ");
            sql.append("ui.real_name as userName ");
            sql.append("from article art ");
            sql.append("left join user_info ui on art.user_id = ui.user_id ");
            if (null == search) {
                sql.append("where art.user_id = ? ");
                sql.append("order by art.id limit ?,? ");
                maps = qr.query(sql.toString(), new MapListHandler(), userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            } else {
                sql.append("where art.title like ? and art.user_id = ? ");
                sql.append("order by art.id limit ?,? ");
                search = search + "%";
                maps = qr.query(sql.toString(), new MapListHandler(), search, userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }

    public List<Map<String, Object>> findFav(Integer userId, int page, String search) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        List<Map<String, Object>> maps = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("select fav.id as favId, fav.u_id as favUId, fav.a_id as favAId, ");
            sql.append("art.title as artTitle,art.content as artCont,art.browse_count as artCount,");
            sql.append("art.publish_date as artDate,art.publish_username as artName,art.user_id as userId,");
            sql.append("(select count(*) as total from favorite fav where art.id = fav.a_id) as follows, ");
            sql.append("ui.real_name as userName ");
            sql.append("from favorite fav ");
            sql.append("left join article art on fav.a_id = art.id ");
            sql.append("left join user_info ui on art.user_id = ui.user_id ");
            if (null == search) {
                sql.append("where fav.u_id = ? ");
                sql.append("order by fav.id limit ?,? ");
                maps = qr.query(sql.toString(), new MapListHandler(), userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            } else {
                sql.append("where art.title like ? and fav.u_id = ? ");
                sql.append("order by art.id limit ?,? ");
                search = search + "%";
                maps = qr.query(sql.toString(), new MapListHandler(), search, userId, (page - 1) * Const.MAX_PAGE_SIZE, Const.MAX_PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return maps;
    }


}
